import pandas as pd
import numpy as np

# 读取车辆信息表
clxx = pd.read_csv('data/ERP_CLXX.csv')

# 读取发货地信息表
fhdxx = pd.read_csv('data/ERP_FHDXX.csv')

# 读取发货记录表
fhjl = pd.read_csv('data/ERP_FHJL.csv')

# 读取客户下单信息表
khxd = pd.read_csv('data/ERP_KHXD.csv')

# 读取客户信息表
khxx = pd.read_csv('data/ERP_KHXX.csv')

# 读取客户站点信息表
zdxx = pd.read_csv('data/ERP_ZDXX.csv')

# 读取系统用户表
sys_user = pd.read_csv('data/sys_user.csv')

# 读取系统字典表
sys_dict_data = pd.read_csv('data/sys_dict_data.csv')


# 客户信息表与其他表关联
sys_user.rename(columns={'user_name': 'user_name_sys'}, inplace=True)
khxd = pd.merge(khxd, sys_user, left_on='create_by', right_on='user_name_sys', how='left', suffixes=('_left', '_right'))
khxd = pd.merge(khxd, khxx, left_on='khxx_id', right_on='id', how='left')
khxd = pd.merge(khxd, zdxx, left_on='zd_id', right_on='id', how='left')

khxd.to_csv('khxd.csv', index=False)

# 发货记录表与其他表关联
fhjl = pd.merge(fhjl, khxx, left_on='khxx_id', right_on='id', how='left')
fhjl = pd.merge(fhjl, zdxx, left_on='zdxx_id', right_on='id', how='left')
fhjl = pd.merge(fhjl, khxd, left_on='khxd_id', right_on='id', how='left')
fhjl = pd.merge(fhjl, fhdxx, left_on='fhdxx_id', right_on='id', how='left')
fhjl = pd.merge(fhjl, clxx, left_on='clxx_id', right_on='nid', how='left')

fhjl.to_csv('fhjl.csv', index=False)

# 合并两个主要表
combined_table = pd.concat([fhjl, khxd], axis=1)
combined_table = combined_table.drop(['id_x_y','khxx_id_x_x','zdxx_id','khxx_id_x_x','khxx_id_x_y','zd_id','hplx_y','cpgg_y','cppp_y','fhd_id','dzsj','create_by','user_name_x','id_y_y','khmc_y','user_name_y','id_y','khxx_id_y_y','zdmc_y','nid','hplx','cppp','cpgg','khxx_id_x','fhdxx_id','ddap_id','id'], axis=1)

# 保存合并后的大表
combined_table.to_csv('combined_table.csv', index=False)


